{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b85cbfe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Py3_Jupyter_Nb_讯捷集团_PPT数据转换(区域-门店-节日)_GF_2024-01-04.ipynb\n",
    "# Create By GF 2024-01-04 17:38"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0fde47f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d541d58b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "# --------------------------------------------------\n",
    "plt.rcParams[\"font.sans-serif\"] = [\"SimHei\"]\n",
    "plt.rcParams[\"axes.unicode_minus\"] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "385dddb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def DtmFunc_Festival_Allocate_Serial_Number(FestivalName:str):\n",
    "\n",
    "    \"\"\"\n",
    "    [Example] FestivalName = \"元旦节\"\n",
    "              Result = DtmFunc_Festival_Allocate_Serial_Number(FestivalName)\n",
    "              \n",
    "              print(Result)\n",
    "              >>> '(1)元旦节'\n",
    "    \"\"\"\n",
    "    if (type(FestivalName) is float): return FestivalName\n",
    "    # ----------------------------------------------\n",
    "    if (\"元旦\" in FestivalName): return \"(1)%s\" % FestivalName\n",
    "    if (\"春节\" in FestivalName): return \"(2)%s\" % FestivalName\n",
    "    if (\"清明\" in FestivalName): return \"(3)%s\" % FestivalName\n",
    "    if (\"劳动\" in FestivalName): return \"(4)%s\" % FestivalName\n",
    "    if (\"端午\" in FestivalName): return \"(5)%s\" % FestivalName\n",
    "    # ..............................................\n",
    "    if (\"中秋\" in FestivalName) and (\"国庆\" in FestivalName): return \"(6)%s\" % FestivalName\n",
    "    if (\"中秋\" in FestivalName) and (\"国庆\" not in FestivalName): return \"(6)%s\" % FestivalName\n",
    "    if (\"国庆\" in FestivalName) and (\"中秋\" not in FestivalName): return \"(7)%s\" % FestivalName\n",
    "    # ..............................................\n",
    "    if (\"圣诞\" in FestivalName): return \"(8)%s\" % FestivalName\n",
    "    \n",
    "    # ##############################################\n",
    "    # End of Function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "07a43b09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>月</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>企业中心</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>门店闭店日期</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>统计年份</th>\n",
       "      <th>标记</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>C</td>\n",
       "      <td>NaT</td>\n",
       "      <td>外围</td>\n",
       "      <td>5463.482</td>\n",
       "      <td>93891.9210</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>华为</td>\n",
       "      <td>三中心</td>\n",
       "      <td>C</td>\n",
       "      <td>NaT</td>\n",
       "      <td>二级</td>\n",
       "      <td>797.060</td>\n",
       "      <td>70332.8087</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>A+1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>二级</td>\n",
       "      <td>23408.760</td>\n",
       "      <td>530026.7020</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1预</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>7217.090</td>\n",
       "      <td>306572.8570</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343433</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343434</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>四中心</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>市区</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343435</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>2372.950</td>\n",
       "      <td>317853.2867</td>\n",
       "      <td>2020</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343436</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>外围</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343437</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>四中心</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>343438 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期   月  周内第几天 企业体系 企业中心 门店等级 门店闭店日期 区域类别         毛利  \\\n",
       "0      2023-12-01  12      6   形象  三中心    C    NaT   外围   5463.482   \n",
       "1      2023-12-01  12      6   华为  三中心    C    NaT   二级    797.060   \n",
       "2      2023-12-01  12      6   形象  三中心  A+1    NaT   二级  23408.760   \n",
       "3      2023-12-01  12      6   形象  二中心  A1预    NaT   郊县        NaN   \n",
       "4      2023-12-01  12      6   形象  三中心   A1    NaT   郊县   7217.090   \n",
       "...           ...  ..    ...  ...  ...  ...    ...  ...        ...   \n",
       "343433 2020-01-31   1      6   形象  二中心   B2    NaT   郊县        NaN   \n",
       "343434 2020-01-31   1      6   形象  四中心   B1    NaT   市区        NaN   \n",
       "343435 2020-01-31   1      6   形象  二中心   A1    NaT   郊县   2372.950   \n",
       "343436 2020-01-31   1      6   形象  二中心   A1    NaT   外围        NaN   \n",
       "343437 2020-01-31   1      6   形象  四中心   B2    NaT   郊县        NaN   \n",
       "\n",
       "              毛利月合计  统计年份       标记  \n",
       "0        93891.9210  2023     None  \n",
       "1        70332.8087  2023     None  \n",
       "2       530026.7020  2023     None  \n",
       "3               NaN  2023  Invalid  \n",
       "4       306572.8570  2023     None  \n",
       "...             ...   ...      ...  \n",
       "343433          NaN  2020  Invalid  \n",
       "343434          NaN  2020  Invalid  \n",
       "343435  317853.2867  2020     None  \n",
       "343436          NaN  2020     None  \n",
       "343437          NaN  2020  Invalid  \n",
       "\n",
       "[343438 rows x 12 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "StoreProfitPDF = pd.read_csv(\"./Datas/CSV数据_讯捷集团_毛利数据_门店毛利数据_2020-01-01至2023-12-31_GB2312.csv\", encoding=\"gb2312\")\n",
    "# ..................................................\n",
    "StoreProfitPDF[\"日期\"] = StoreProfitPDF[\"日期\"].astype(\"datetime64[ns]\")\n",
    "StoreProfitPDF[\"门店开店日期\"] = StoreProfitPDF[\"门店开店日期\"].astype(\"datetime64[ns]\")\n",
    "StoreProfitPDF[\"门店闭店日期\"] = StoreProfitPDF[\"门店闭店日期\"].astype(\"datetime64[ns]\")\n",
    "# ..................................................\n",
    "StoreProfitPDF[\"统计日期\"] = StoreProfitPDF[\"日期\"]\n",
    "StoreProfitPDF[\"统计年份\"] = StoreProfitPDF[\"日期\"].dt.year\n",
    "StoreProfitPDF[\"统计月份\"] = StoreProfitPDF[\"日期\"].dt.month\n",
    "StoreProfitPDF[\"统计月日\"] = StoreProfitPDF[\"日期\"].dt.strftime(\"%m-%d\")\n",
    "# ..................................................\n",
    "StoreProfitPDF[\"标记\"] = None\n",
    "# ..................................................\n",
    "StoreProfitPDF.loc[StoreProfitPDF[StoreProfitPDF[\"统计日期\"] < StoreProfitPDF[\"门店开店日期\"]].index, \"标记\"] = str(\"Invalid\")\n",
    "StoreProfitPDF.loc[StoreProfitPDF[StoreProfitPDF[\"统计日期\"] < StoreProfitPDF[\"门店开店日期\"]].index, \"门店闭店日期\"] = pd.NaT\n",
    "\n",
    "# ##################################################\n",
    "StoreProfitPDF[StoreProfitPDF.columns[::3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b65ede30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>节日所属年份</th>\n",
       "      <th>节日标注</th>\n",
       "      <th>节日名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>节日</td>\n",
       "      <td>元旦</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1822</th>\n",
       "      <td>2024-12-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1823</th>\n",
       "      <td>2024-12-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1824</th>\n",
       "      <td>2024-12-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1825</th>\n",
       "      <td>2024-12-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1826</th>\n",
       "      <td>2024-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1827 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             日期  节日所属年份 节日标注 节日名称\n",
       "0    2020-01-01  2020.0   节日   元旦\n",
       "1    2020-01-02     NaN  NaN  NaN\n",
       "2    2020-01-03     NaN  NaN  NaN\n",
       "3    2020-01-04     NaN  NaN  NaN\n",
       "4    2020-01-05     NaN  NaN  NaN\n",
       "...         ...     ...  ...  ...\n",
       "1822 2024-12-27     NaN  NaN  NaN\n",
       "1823 2024-12-28     NaN  NaN  NaN\n",
       "1824 2024-12-29     NaN  NaN  NaN\n",
       "1825 2024-12-30     NaN  NaN  NaN\n",
       "1826 2024-12-31     NaN  NaN  NaN\n",
       "\n",
       "[1827 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CalendarPDF = pd.read_excel(\"./Datas/日历_2020-2024_节日及事件_GF_2024-01-04.xlsx\", sheet_name=\"Sheet1\")\n",
    "# ..................................................\n",
    "CalendarFestivalPDF = CalendarPDF[[\"日期\", \"节日所属年份\", \"节日标注\", \"节日名称\"]]\n",
    "\n",
    "# ##################################################\n",
    "CalendarFestivalPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "05f29645",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>地区</th>\n",
       "      <th>部门</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>经营时间</th>\n",
       "      <th>关店年份</th>\n",
       "      <th>门店关店日期</th>\n",
       "      <th>闭店类型</th>\n",
       "      <th>商圈类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>四川</td>\n",
       "      <td>零售一中心</td>\n",
       "      <td>四川成都青羊万达华为店</td>\n",
       "      <td>2020-04-01 00:00:00</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023-03-09</td>\n",
       "      <td>到期闭店退租</td>\n",
       "      <td>综合体外</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>四川</td>\n",
       "      <td>零售一中心</td>\n",
       "      <td>四川自贡龙都形象店</td>\n",
       "      <td>2012-05-01 00:00:00</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023-07-12</td>\n",
       "      <td>提前闭店退租</td>\n",
       "      <td>街铺+复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>四川</td>\n",
       "      <td>零售三中心</td>\n",
       "      <td>四川成都高新万达形象店</td>\n",
       "      <td>2023-01-05 00:00:00</td>\n",
       "      <td>2023</td>\n",
       "      <td>2024-12-31</td>\n",
       "      <td>到期闭店退租</td>\n",
       "      <td>综合体内</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>四川</td>\n",
       "      <td>零售四中心</td>\n",
       "      <td>四川成都光华形象店</td>\n",
       "      <td>2021-12-05 00:00:00</td>\n",
       "      <td>2023</td>\n",
       "      <td>2023-03-30</td>\n",
       "      <td>提前闭店退租</td>\n",
       "      <td>街铺+复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>四川</td>\n",
       "      <td>零售四中心</td>\n",
       "      <td>四川内江资中形象店</td>\n",
       "      <td>2021-07-02 00:00:00</td>\n",
       "      <td>2023</td>\n",
       "      <td>2024-11-13</td>\n",
       "      <td>提前闭店退租</td>\n",
       "      <td>街铺+复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>85</td>\n",
       "      <td>贵州</td>\n",
       "      <td>零售二中心</td>\n",
       "      <td>贵州遵义形象店</td>\n",
       "      <td>2011-06-01 00:00:00</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-06-11</td>\n",
       "      <td>提前闭店退租</td>\n",
       "      <td>街铺+复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>86</td>\n",
       "      <td>贵州</td>\n",
       "      <td>零售二中心</td>\n",
       "      <td>贵州金阳形象店</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>2024-07-25</td>\n",
       "      <td>房东收回</td>\n",
       "      <td>综合体内</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>87</td>\n",
       "      <td>重庆</td>\n",
       "      <td>零售一中心</td>\n",
       "      <td>重庆李家沱正街形象店</td>\n",
       "      <td>2019-08-01 00:00:00</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-02-29</td>\n",
       "      <td>提前闭店退租</td>\n",
       "      <td>街铺+复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>88</td>\n",
       "      <td>重庆</td>\n",
       "      <td>零售一中心</td>\n",
       "      <td>重庆解放碑华为店</td>\n",
       "      <td>2019-04-01 00:00:00</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-04-18</td>\n",
       "      <td>提前闭店退租</td>\n",
       "      <td>街铺+复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>89</td>\n",
       "      <td>重庆</td>\n",
       "      <td>零售一中心</td>\n",
       "      <td>重庆垫江西欧步行街形象店</td>\n",
       "      <td>2019-04-01 00:00:00</td>\n",
       "      <td>2020</td>\n",
       "      <td>2020-08-11</td>\n",
       "      <td>提前闭店退租</td>\n",
       "      <td>街铺+复合商圈</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>89 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    序号  地区     部门          门店名称                 经营时间  关店年份     门店关店日期    闭店类型  \\\n",
       "0    1  四川  零售一中心   四川成都青羊万达华为店  2020-04-01 00:00:00  2023 2023-03-09  到期闭店退租   \n",
       "1    2  四川  零售一中心     四川自贡龙都形象店  2012-05-01 00:00:00  2023 2023-07-12  提前闭店退租   \n",
       "2    3  四川  零售三中心   四川成都高新万达形象店  2023-01-05 00:00:00  2023 2024-12-31  到期闭店退租   \n",
       "3    4  四川  零售四中心     四川成都光华形象店  2021-12-05 00:00:00  2023 2023-03-30  提前闭店退租   \n",
       "4    5  四川  零售四中心     四川内江资中形象店  2021-07-02 00:00:00  2023 2024-11-13  提前闭店退租   \n",
       "..  ..  ..    ...           ...                  ...   ...        ...     ...   \n",
       "84  85  贵州  零售二中心       贵州遵义形象店  2011-06-01 00:00:00  2020 2020-06-11  提前闭店退租   \n",
       "85  86  贵州  零售二中心       贵州金阳形象店                  NaN  2020 2024-07-25    房东收回   \n",
       "86  87  重庆  零售一中心    重庆李家沱正街形象店  2019-08-01 00:00:00  2020 2020-02-29  提前闭店退租   \n",
       "87  88  重庆  零售一中心      重庆解放碑华为店  2019-04-01 00:00:00  2020 2020-04-18  提前闭店退租   \n",
       "88  89  重庆  零售一中心  重庆垫江西欧步行街形象店  2019-04-01 00:00:00  2020 2020-08-11  提前闭店退租   \n",
       "\n",
       "       商圈类型  \n",
       "0      综合体外  \n",
       "1   街铺+复合商圈  \n",
       "2      综合体内  \n",
       "3   街铺+复合商圈  \n",
       "4   街铺+复合商圈  \n",
       "..      ...  \n",
       "84  街铺+复合商圈  \n",
       "85     综合体内  \n",
       "86  街铺+复合商圈  \n",
       "87  街铺+复合商圈  \n",
       "88  街铺+复合商圈  \n",
       "\n",
       "[89 rows x 9 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ClosedStorePDF = pd.read_excel(\"./Datas/2020-2023闭店.xlsx\", sheet_name=\"关闭门店\")\n",
    "# ..................................................\n",
    "ClosedStorePDF = ClosedStorePDF.rename(columns={\"门店\":\"门店名称\", \"闭店年份\":\"关店年份\", \"闭店时间\":\"门店关店日期\"})\n",
    "# ##################################################\n",
    "ClosedStorePDF[ClosedStorePDF.columns[0:9]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6b7c6333",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>统计年份</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "      <th>区域店均毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>7429.352968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>11063.612260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>6212.480585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>16629.425360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>三部</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>8506.294909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>市区</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>5475.977429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>11764.656781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>郊县</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>7783.639532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>7193.645343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>2023</td>\n",
       "      <td>综合</td>\n",
       "      <td>一部</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>20087.066508</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>194 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     统计年份 企业体系 企业部门 区域类别     区域商圈        区域店均毛利\n",
       "168  2022   华为   一部   外围  街铺-复合商圈   7429.352968\n",
       "169  2022   华为   一部   市区  街铺-通讯商圈  11063.612260\n",
       "170  2022   华为   一部   郊县  街铺-复合商圈   6212.480585\n",
       "171  2022   华为   一部   郊县  街铺-通讯商圈  16629.425360\n",
       "172  2022   华为   三部   外围  街铺-复合商圈   8506.294909\n",
       "..    ...  ...  ...  ...      ...           ...\n",
       "357  2023   形象   重庆   市区   综合体外商圈   5475.977429\n",
       "358  2023   形象   重庆   市区  街铺-复合商圈  11764.656781\n",
       "359  2023   形象   重庆   郊县   综合体外商圈   7783.639532\n",
       "360  2023   形象   重庆   郊县  街铺-复合商圈   7193.645343\n",
       "361  2023   综合   一部   市区  街铺-通讯商圈  20087.066508\n",
       "\n",
       "[194 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 区域店均。\n",
    "# ##################################################\n",
    "SourceDataPDF = StoreProfitPDF[StoreProfitPDF[\"标记\"] != \"Invalid\"].copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 聚合数据: 区域数据按 [统计年份] 聚合各区域 [区域店均毛利]。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = SourceDataPDF[[\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\", \"毛利\"]]\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF.groupby([\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\"], as_index=False).mean(\"毛利\")\n",
    "# ..................................................\n",
    "RegionStoreAvgPDF = TEMPPDF.rename(columns={\"毛利\":\"区域店均毛利\"})\n",
    "\n",
    "# 保存数据。\n",
    "# --------------------------------------------------\n",
    "RegionStoreAvgPDF = RegionStoreAvgPDF[RegionStoreAvgPDF[\"统计年份\"] >= 2022]\n",
    "RegionStoreAvgPDF.to_excel(\"./Demo.xlsx\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "RegionStoreAvgPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fe0c6927",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>统计年份</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "      <th>区域合计毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2.600274e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>7.290920e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>4.292824e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>5.920075e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>2022</td>\n",
       "      <td>华为</td>\n",
       "      <td>三部</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2.994216e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>市区</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>1.987780e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2.512931e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>郊县</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>2.778759e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>3.346484e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>2023</td>\n",
       "      <td>综合</td>\n",
       "      <td>一部</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>7.331779e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>194 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     统计年份 企业体系 企业部门 区域类别     区域商圈        区域合计毛利\n",
       "168  2022   华为   一部   外围  街铺-复合商圈  2.600274e+06\n",
       "169  2022   华为   一部   市区  街铺-通讯商圈  7.290920e+06\n",
       "170  2022   华为   一部   郊县  街铺-复合商圈  4.292824e+06\n",
       "171  2022   华为   一部   郊县  街铺-通讯商圈  5.920075e+06\n",
       "172  2022   华为   三部   外围  街铺-复合商圈  2.994216e+06\n",
       "..    ...  ...  ...  ...      ...           ...\n",
       "357  2023   形象   重庆   市区   综合体外商圈  1.987780e+06\n",
       "358  2023   形象   重庆   市区  街铺-复合商圈  2.512931e+07\n",
       "359  2023   形象   重庆   郊县   综合体外商圈  2.778759e+06\n",
       "360  2023   形象   重庆   郊县  街铺-复合商圈  3.346484e+07\n",
       "361  2023   综合   一部   市区  街铺-通讯商圈  7.331779e+06\n",
       "\n",
       "[194 rows x 6 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 区域合计。\n",
    "# ##################################################\n",
    "SourceDataPDF = StoreProfitPDF[StoreProfitPDF[\"标记\"] != \"Invalid\"].copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 聚合数据: 区域数据按 [统计年份] 聚合各区域 [区域合计毛利]。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = SourceDataPDF[[\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\", \"毛利\"]]\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF.groupby([\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\"], as_index=False).sum(\"毛利\")\n",
    "# ..................................................\n",
    "RegionStoreSumPDF = TEMPPDF.rename(columns={\"毛利\":\"区域合计毛利\"})\n",
    "\n",
    "# 保存数据。\n",
    "# --------------------------------------------------\n",
    "RegionStoreSumPDF = RegionStoreSumPDF[RegionStoreSumPDF[\"统计年份\"] >= 2022]\n",
    "RegionStoreSumPDF.to_excel(\"./Demo.xlsx\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "RegionStoreSumPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ea8b093d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>统计年份</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "      <th>门店开店日期</th>\n",
       "      <th>门店日均毛利</th>\n",
       "      <th>门店年总毛利</th>\n",
       "      <th>区域店均毛利</th>\n",
       "      <th>区域合计毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州</td>\n",
       "      <td>贵州毕节黔西形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023-03-26</td>\n",
       "      <td>3311.809489</td>\n",
       "      <td>9.041240e+05</td>\n",
       "      <td>6345.704925</td>\n",
       "      <td>4.048560e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023</td>\n",
       "      <td>华为</td>\n",
       "      <td>贵州</td>\n",
       "      <td>贵州开阳华为店</td>\n",
       "      <td>C</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2016-12-05</td>\n",
       "      <td>3043.170828</td>\n",
       "      <td>1.095541e+06</td>\n",
       "      <td>5293.401920</td>\n",
       "      <td>5.769808e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州</td>\n",
       "      <td>贵州遵义丁字口形象店</td>\n",
       "      <td>A+1</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>20123.796222</td>\n",
       "      <td>7.345186e+06</td>\n",
       "      <td>13402.800260</td>\n",
       "      <td>5.018008e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州</td>\n",
       "      <td>贵州贵阳黄河形象店</td>\n",
       "      <td>A1</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>9941.235031</td>\n",
       "      <td>3.628551e+06</td>\n",
       "      <td>13661.996864</td>\n",
       "      <td>2.487850e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州</td>\n",
       "      <td>贵州凯里新世纪形象店</td>\n",
       "      <td>A2</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>17897.058565</td>\n",
       "      <td>6.532426e+06</td>\n",
       "      <td>15085.075179</td>\n",
       "      <td>1.101210e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆石桥铺形象店</td>\n",
       "      <td>B1</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>4402.926760</td>\n",
       "      <td>1.404534e+06</td>\n",
       "      <td>8849.238550</td>\n",
       "      <td>1.568085e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>西安</td>\n",
       "      <td>陕西西安东大街形象店</td>\n",
       "      <td>A+1</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>21278.008259</td>\n",
       "      <td>4.766274e+06</td>\n",
       "      <td>11040.777014</td>\n",
       "      <td>1.049978e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>云南</td>\n",
       "      <td>云南昆明南疆形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>5023.969293</td>\n",
       "      <td>5.425887e+05</td>\n",
       "      <td>12739.576060</td>\n",
       "      <td>1.532571e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>四部</td>\n",
       "      <td>四川成都光华形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>4311.079551</td>\n",
       "      <td>3.836861e+05</td>\n",
       "      <td>6213.732069</td>\n",
       "      <td>5.076619e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>2023</td>\n",
       "      <td>华为</td>\n",
       "      <td>一部</td>\n",
       "      <td>四川大邑东街华为店</td>\n",
       "      <td>C</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2019-08-31</td>\n",
       "      <td>7657.297386</td>\n",
       "      <td>3.369211e+05</td>\n",
       "      <td>7966.894944</td>\n",
       "      <td>3.258460e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>234 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     统计年份 企业体系 企业部门        门店名称 门店等级 区域类别     区域商圈     门店开店日期        门店日均毛利  \\\n",
       "0    2023   形象   贵州   贵州毕节黔西形象店    C   外围  街铺-复合商圈 2023-03-26   3311.809489   \n",
       "1    2023   华为   贵州     贵州开阳华为店    C   二级  街铺-复合商圈 2016-12-05   3043.170828   \n",
       "2    2023   形象   贵州  贵州遵义丁字口形象店  A+1   二级  街铺-复合商圈 2014-01-01  20123.796222   \n",
       "3    2023   形象   贵州   贵州贵阳黄河形象店   A1   郊县  街铺-复合商圈 2014-01-01   9941.235031   \n",
       "4    2023   形象   贵州  贵州凯里新世纪形象店   A2   二级  街铺-通讯商圈 2014-01-01  17897.058565   \n",
       "..    ...  ...  ...         ...  ...  ...      ...        ...           ...   \n",
       "229  2023   形象   重庆    重庆石桥铺形象店   B1   外围  街铺-复合商圈 2014-01-01   4402.926760   \n",
       "230  2023   形象   西安  陕西西安东大街形象店  A+1   市区  街铺-复合商圈 2014-01-01  21278.008259   \n",
       "231  2023   形象   云南   云南昆明南疆形象店    C   市区  街铺-通讯商圈 2020-07-27   5023.969293   \n",
       "232  2023   形象   四部   四川成都光华形象店    C   外围  街铺-复合商圈 2014-08-01   4311.079551   \n",
       "233  2023   华为   一部   四川大邑东街华为店    C   郊县  街铺-复合商圈 2019-08-31   7657.297386   \n",
       "\n",
       "           门店年总毛利        区域店均毛利        区域合计毛利  \n",
       "0    9.041240e+05   6345.704925  4.048560e+06  \n",
       "1    1.095541e+06   5293.401920  5.769808e+06  \n",
       "2    7.345186e+06  13402.800260  5.018008e+07  \n",
       "3    3.628551e+06  13661.996864  2.487850e+07  \n",
       "4    6.532426e+06  15085.075179  1.101210e+07  \n",
       "..            ...           ...           ...  \n",
       "229  1.404534e+06   8849.238550  1.568085e+07  \n",
       "230  4.766274e+06  11040.777014  1.049978e+07  \n",
       "231  5.425887e+05  12739.576060  1.532571e+07  \n",
       "232  3.836861e+05   6213.732069  5.076619e+06  \n",
       "233  3.369211e+05   7966.894944  3.258460e+06  \n",
       "\n",
       "[234 rows x 12 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 所有门店总览。\n",
    "# ##################################################\n",
    "SourceDataPDF = StoreProfitPDF[StoreProfitPDF[\"标记\"] != \"Invalid\"].copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 数据去重: 门店数据按 [统计年份] 去重。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = SourceDataPDF[[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\", \"门店等级\", \"区域类别\", \"区域商圈\", \"门店开店日期\"]]\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF.drop_duplicates(subset=[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"])\n",
    "# ..................................................\n",
    "DropDuplicatedPDF = TEMPPDF\n",
    "\n",
    "# 聚合数据: 门店数据按 [统计年份] 聚合各门店 [门店平均毛利]。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = SourceDataPDF[[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\", \"门店等级\", \"毛利\"]]\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF.groupby([\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"], as_index=False).mean(\"毛利\")\n",
    "# ..................................................\n",
    "StoreProfitYearDaysAvgPDF = TEMPPDF.rename(columns={\"毛利\":\"门店日均毛利\"})\n",
    "\n",
    "# 聚合数据: 门店数据按 [统计年份] 聚合各门店 [门店年总毛利]。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = SourceDataPDF[[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\", \"门店等级\", \"毛利\"]]\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF.groupby([\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"], as_index=False).sum(\"毛利\")\n",
    "# ..................................................\n",
    "StoreProfitYearSumPDF = TEMPPDF.rename(columns={\"毛利\":\"门店年总毛利\"})\n",
    "\n",
    "# 数据连接: 合并 [门店等级] 数据和 [年日均毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = DropDuplicatedPDF\n",
    "RIGHPDF = StoreProfitYearDaysAvgPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"])\n",
    "# ..................................................\n",
    "AllStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接拉取 [门店年总毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = AllStoreOverviewPDF\n",
    "RIGHPDF = StoreProfitYearSumPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"])\n",
    "# ..................................................\n",
    "AllStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接拉取 [区域店均毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = AllStoreOverviewPDF\n",
    "RIGHPDF = RegionStoreAvgPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\"])\n",
    "# ..................................................\n",
    "AllStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接拉取 [区域合计毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = AllStoreOverviewPDF\n",
    "RIGHPDF = RegionStoreSumPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\"])\n",
    "# ..................................................\n",
    "AllStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 保存数据。\n",
    "# --------------------------------------------------\n",
    "AllStoreOverviewPDF = AllStoreOverviewPDF[AllStoreOverviewPDF[\"统计年份\"] == 2023]\n",
    "AllStoreOverviewPDF.to_excel(\"./Demo.xlsx\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "AllStoreOverviewPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8cfda4a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>统计年份</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>门店开店日期</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "      <th>关店年份</th>\n",
       "      <th>门店关店日期</th>\n",
       "      <th>存续周期(天)</th>\n",
       "      <th>门店日均毛利</th>\n",
       "      <th>门店年总毛利</th>\n",
       "      <th>区域店均毛利</th>\n",
       "      <th>区域合计毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>三部</td>\n",
       "      <td>四川成都高新万达形象店</td>\n",
       "      <td>D</td>\n",
       "      <td>2023-01-05</td>\n",
       "      <td>郊县</td>\n",
       "      <td>综合体内商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>360.0</td>\n",
       "      <td>1324.390553</td>\n",
       "      <td>3.787757e+05</td>\n",
       "      <td>8288.061437</td>\n",
       "      <td>5.395528e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>一部</td>\n",
       "      <td>四川自贡龙都形象店</td>\n",
       "      <td>B1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-07-12</td>\n",
       "      <td>3479.0</td>\n",
       "      <td>7438.081860</td>\n",
       "      <td>2.707462e+06</td>\n",
       "      <td>7952.159303</td>\n",
       "      <td>1.127616e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆江北步行街形象店</td>\n",
       "      <td>A1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>12044.800411</td>\n",
       "      <td>4.396352e+06</td>\n",
       "      <td>11764.656781</td>\n",
       "      <td>2.512931e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州</td>\n",
       "      <td>贵州盘州形象店</td>\n",
       "      <td>B2</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-06-25</td>\n",
       "      <td>1269.0</td>\n",
       "      <td>8949.626942</td>\n",
       "      <td>3.007075e+06</td>\n",
       "      <td>13402.800260</td>\n",
       "      <td>5.018008e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>四部</td>\n",
       "      <td>四川内江资中形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>2021-07-02</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-11-13</td>\n",
       "      <td>864.0</td>\n",
       "      <td>4083.122584</td>\n",
       "      <td>1.180022e+06</td>\n",
       "      <td>12456.252467</td>\n",
       "      <td>2.115072e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆石桥铺形象店</td>\n",
       "      <td>B1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-11-23</td>\n",
       "      <td>3613.0</td>\n",
       "      <td>4402.926760</td>\n",
       "      <td>1.404534e+06</td>\n",
       "      <td>8849.238550</td>\n",
       "      <td>1.568085e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>西安</td>\n",
       "      <td>陕西西安东大街形象店</td>\n",
       "      <td>A+1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-08-16</td>\n",
       "      <td>3514.0</td>\n",
       "      <td>21278.008259</td>\n",
       "      <td>4.766274e+06</td>\n",
       "      <td>11040.777014</td>\n",
       "      <td>1.049978e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>云南</td>\n",
       "      <td>云南昆明南疆形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-04-18</td>\n",
       "      <td>995.0</td>\n",
       "      <td>5023.969293</td>\n",
       "      <td>5.425887e+05</td>\n",
       "      <td>12739.576060</td>\n",
       "      <td>1.532571e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>2023</td>\n",
       "      <td>形象</td>\n",
       "      <td>四部</td>\n",
       "      <td>四川成都光华形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-03-30</td>\n",
       "      <td>3163.0</td>\n",
       "      <td>4311.079551</td>\n",
       "      <td>3.836861e+05</td>\n",
       "      <td>6213.732069</td>\n",
       "      <td>5.076619e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>西安</td>\n",
       "      <td>陕西西安东大街形象店</td>\n",
       "      <td>A+1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-08-16</td>\n",
       "      <td>3514.0</td>\n",
       "      <td>24235.648651</td>\n",
       "      <td>7.852350e+06</td>\n",
       "      <td>13157.776700</td>\n",
       "      <td>1.303936e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>四部</td>\n",
       "      <td>四川成都光华形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-03-30</td>\n",
       "      <td>3163.0</td>\n",
       "      <td>4184.162602</td>\n",
       "      <td>1.443536e+06</td>\n",
       "      <td>5026.516394</td>\n",
       "      <td>5.232604e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州</td>\n",
       "      <td>贵州盘州形象店</td>\n",
       "      <td>B2</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-06-25</td>\n",
       "      <td>1269.0</td>\n",
       "      <td>7369.388011</td>\n",
       "      <td>2.689827e+06</td>\n",
       "      <td>11403.966221</td>\n",
       "      <td>3.697166e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆石桥铺形象店</td>\n",
       "      <td>B1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-11-23</td>\n",
       "      <td>3613.0</td>\n",
       "      <td>6696.436270</td>\n",
       "      <td>2.250003e+06</td>\n",
       "      <td>9755.861458</td>\n",
       "      <td>1.649716e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>四部</td>\n",
       "      <td>四川内江资中形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>2021-07-02</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-11-13</td>\n",
       "      <td>864.0</td>\n",
       "      <td>3880.485212</td>\n",
       "      <td>1.288321e+06</td>\n",
       "      <td>10511.374889</td>\n",
       "      <td>1.841593e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>410</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆江北步行街形象店</td>\n",
       "      <td>A1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>3060.0</td>\n",
       "      <td>10468.262557</td>\n",
       "      <td>3.255630e+06</td>\n",
       "      <td>11222.338193</td>\n",
       "      <td>2.202945e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>414</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>一部</td>\n",
       "      <td>四川自贡龙都形象店</td>\n",
       "      <td>B1</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-07-12</td>\n",
       "      <td>3479.0</td>\n",
       "      <td>5274.383990</td>\n",
       "      <td>1.904053e+06</td>\n",
       "      <td>8139.534417</td>\n",
       "      <td>8.546511e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>428</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>云南</td>\n",
       "      <td>云南昆明南疆形象店</td>\n",
       "      <td>C</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023-04-18</td>\n",
       "      <td>995.0</td>\n",
       "      <td>4298.581635</td>\n",
       "      <td>1.551788e+06</td>\n",
       "      <td>9627.635665</td>\n",
       "      <td>1.401784e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆</td>\n",
       "      <td>重庆北碚万达形象店</td>\n",
       "      <td>B2</td>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>1682.0</td>\n",
       "      <td>10988.987842</td>\n",
       "      <td>9.120860e+05</td>\n",
       "      <td>7250.408396</td>\n",
       "      <td>5.546562e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>2022</td>\n",
       "      <td>形象</td>\n",
       "      <td>四部</td>\n",
       "      <td>四川龙泉通达形象店</td>\n",
       "      <td>A2</td>\n",
       "      <td>2016-03-20</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022-02-20</td>\n",
       "      <td>2163.0</td>\n",
       "      <td>15851.886494</td>\n",
       "      <td>8.084462e+05</td>\n",
       "      <td>12412.431306</td>\n",
       "      <td>1.331854e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     统计年份 企业体系 企业部门         门店名称 门店等级     门店开店日期 区域类别     区域商圈    关店年份  \\\n",
       "21   2023   形象   三部  四川成都高新万达形象店    D 2023-01-05   郊县   综合体内商圈  2023.0   \n",
       "88   2023   形象   一部    四川自贡龙都形象店   B1 2014-01-01   二级  街铺-复合商圈  2023.0   \n",
       "94   2023   形象   重庆   重庆江北步行街形象店   A1 2014-01-01   市区  街铺-复合商圈  2022.0   \n",
       "105  2023   形象   贵州      贵州盘州形象店   B2 2020-01-03   二级  街铺-复合商圈  2023.0   \n",
       "228  2023   形象   四部    四川内江资中形象店    C 2021-07-02   二级  街铺-复合商圈  2023.0   \n",
       "229  2023   形象   重庆     重庆石桥铺形象店   B1 2014-01-01   外围  街铺-复合商圈  2023.0   \n",
       "230  2023   形象   西安   陕西西安东大街形象店  A+1 2014-01-01   市区  街铺-复合商圈  2023.0   \n",
       "231  2023   形象   云南    云南昆明南疆形象店    C 2020-07-27   市区  街铺-通讯商圈  2023.0   \n",
       "232  2023   形象   四部    四川成都光华形象店    C 2014-08-01   外围  街铺-复合商圈  2023.0   \n",
       "261  2022   形象   西安   陕西西安东大街形象店  A+1 2014-01-01   市区  街铺-复合商圈  2023.0   \n",
       "290  2022   形象   四部    四川成都光华形象店    C 2014-08-01   外围  街铺-复合商圈  2023.0   \n",
       "300  2022   形象   贵州      贵州盘州形象店   B2 2020-01-03   二级  街铺-复合商圈  2023.0   \n",
       "375  2022   形象   重庆     重庆石桥铺形象店   B1 2014-01-01   外围  街铺-复合商圈  2023.0   \n",
       "392  2022   形象   四部    四川内江资中形象店    C 2021-07-02   二级  街铺-复合商圈  2023.0   \n",
       "410  2022   形象   重庆   重庆江北步行街形象店   A1 2014-01-01   市区  街铺-复合商圈  2022.0   \n",
       "414  2022   形象   一部    四川自贡龙都形象店   B1 2014-01-01   二级  街铺-复合商圈  2023.0   \n",
       "428  2022   形象   云南    云南昆明南疆形象店    C 2020-07-27   市区  街铺-通讯商圈  2023.0   \n",
       "438  2022   形象   重庆    重庆北碚万达形象店   B2 2017-08-15   外围   综合体内商圈  2022.0   \n",
       "439  2022   形象   四部    四川龙泉通达形象店   A2 2016-03-20   郊县  街铺-复合商圈  2022.0   \n",
       "\n",
       "        门店关店日期  存续周期(天)        门店日均毛利        门店年总毛利        区域店均毛利  \\\n",
       "21  2023-12-31    360.0   1324.390553  3.787757e+05   8288.061437   \n",
       "88  2023-07-12   3479.0   7438.081860  2.707462e+06   7952.159303   \n",
       "94  2022-05-19   3060.0  12044.800411  4.396352e+06  11764.656781   \n",
       "105 2023-06-25   1269.0   8949.626942  3.007075e+06  13402.800260   \n",
       "228 2023-11-13    864.0   4083.122584  1.180022e+06  12456.252467   \n",
       "229 2023-11-23   3613.0   4402.926760  1.404534e+06   8849.238550   \n",
       "230 2023-08-16   3514.0  21278.008259  4.766274e+06  11040.777014   \n",
       "231 2023-04-18    995.0   5023.969293  5.425887e+05  12739.576060   \n",
       "232 2023-03-30   3163.0   4311.079551  3.836861e+05   6213.732069   \n",
       "261 2023-08-16   3514.0  24235.648651  7.852350e+06  13157.776700   \n",
       "290 2023-03-30   3163.0   4184.162602  1.443536e+06   5026.516394   \n",
       "300 2023-06-25   1269.0   7369.388011  2.689827e+06  11403.966221   \n",
       "375 2023-11-23   3613.0   6696.436270  2.250003e+06   9755.861458   \n",
       "392 2023-11-13    864.0   3880.485212  1.288321e+06  10511.374889   \n",
       "410 2022-05-19   3060.0  10468.262557  3.255630e+06  11222.338193   \n",
       "414 2023-07-12   3479.0   5274.383990  1.904053e+06   8139.534417   \n",
       "428 2023-04-18    995.0   4298.581635  1.551788e+06   9627.635665   \n",
       "438 2022-03-24   1682.0  10988.987842  9.120860e+05   7250.408396   \n",
       "439 2022-02-20   2163.0  15851.886494  8.084462e+05  12412.431306   \n",
       "\n",
       "           区域合计毛利  \n",
       "21   5.395528e+06  \n",
       "88   1.127616e+07  \n",
       "94   2.512931e+07  \n",
       "105  5.018008e+07  \n",
       "228  2.115072e+07  \n",
       "229  1.568085e+07  \n",
       "230  1.049978e+07  \n",
       "231  1.532571e+07  \n",
       "232  5.076619e+06  \n",
       "261  1.303936e+07  \n",
       "290  5.232604e+06  \n",
       "300  3.697166e+07  \n",
       "375  1.649716e+07  \n",
       "392  1.841593e+07  \n",
       "410  2.202945e+07  \n",
       "414  8.546511e+06  \n",
       "428  1.401784e+07  \n",
       "438  5.546562e+06  \n",
       "439  1.331854e+07  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 已关门店总览。\n",
    "# ##################################################\n",
    "SourceDataPDF = StoreProfitPDF[StoreProfitPDF[\"标记\"] != \"Invalid\"].copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 数据去重: 门店数据按 [统计年份] 去重。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = SourceDataPDF[[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\", \"门店等级\", \"门店开店日期\", \"区域类别\", \"区域商圈\"]]\n",
    "# ..................................................\n",
    "TEMPPDF = TEMPPDF.drop_duplicates(subset=[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"])\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接对应关店数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = ClosedStoreOverviewPDF\n",
    "RIGHPDF = ClosedStorePDF[[\"关店年份\", \"门店名称\", \"门店关店日期\"]]\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"门店名称\"])\n",
    "# ..................................................\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据修复。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = ClosedStoreOverviewPDF\n",
    "# ..................................................\n",
    "TEMPPDF[\"关店年份\"] = TEMPPDF[\"关店年份\"].fillna(value=np.nan)\n",
    "TEMPPDF[\"门店关店日期\"] = TEMPPDF[\"门店关店日期\"].fillna(value=pd.NaT)\n",
    "# ..................................................\n",
    "for Idx in TEMPPDF.index:\n",
    "    if   (TEMPPDF.loc[Idx, \"关店年份\"] == np.nan) or (TEMPPDF.loc[Idx, \"门店关店日期\"] == pd.NaT):\n",
    "        continue\n",
    "    elif (TEMPPDF.loc[Idx, \"关店年份\"] != TEMPPDF.loc[Idx, \"门店关店日期\"].year):\n",
    "        TEMPPDF.loc[Idx, \"门店关店日期\"] = TEMPPDF.loc[Idx, \"门店关店日期\"].replace(year=2023)\n",
    "# ..................................................\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据计算。\n",
    "# --------------------------------------------------\n",
    "TEMPPDF = ClosedStoreOverviewPDF\n",
    "# ..................................................\n",
    "TEMPPDF[\"存续周期(天)\"] = TEMPPDF[\"门店关店日期\"] - TEMPPDF[\"门店开店日期\"]\n",
    "TEMPPDF[\"存续周期(天)\"] = TEMPPDF[\"存续周期(天)\"].dt.days\n",
    "# ..................................................\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接拉取 [年日均毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = ClosedStoreOverviewPDF\n",
    "RIGHPDF = StoreProfitYearDaysAvgPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"])\n",
    "# ..................................................\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接拉取 [门店年总毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = ClosedStoreOverviewPDF\n",
    "RIGHPDF = StoreProfitYearSumPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"门店名称\"])\n",
    "# ..................................................\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接拉取 [区域店均毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = ClosedStoreOverviewPDF\n",
    "RIGHPDF = RegionStoreAvgPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\"])\n",
    "# ..................................................\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# 数据连接: 外连接拉取 [区域合计毛利] 数据。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = ClosedStoreOverviewPDF\n",
    "RIGHPDF = RegionStoreSumPDF\n",
    "# ..................................................\n",
    "TEMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业体系\", \"企业部门\", \"区域类别\", \"区域商圈\"])\n",
    "# ..................................................\n",
    "ClosedStoreOverviewPDF = TEMPPDF\n",
    "\n",
    "# ##################################################\n",
    "ClosedStoreOverviewPDF = ClosedStoreOverviewPDF[ClosedStoreOverviewPDF[\"门店关店日期\"].isna() == False]\n",
    "ClosedStoreOverviewPDF = ClosedStoreOverviewPDF[ClosedStoreOverviewPDF[\"关店年份\"] >= 2022]\n",
    "ClosedStoreOverviewPDF = ClosedStoreOverviewPDF[ClosedStoreOverviewPDF[\"统计年份\"] >= 2022]\n",
    "ClosedStoreOverviewPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5c0f6186",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
